MySQL操作语言[DML]

发表于 | 分类于

insert操作

1
insert into table_name (field1, field2,...fieldN) values (value1, value2,...valueN);

如果ID值设置为自增长，列名及其对应的值可以省略不写，但是下面这种写法除外，且值要与列名严格对应

1
insert into table_name values (value1, value2,...valueN);

对于数据，字符串需要添加“ ”，数字不用。列名则都不需要

update操作

1
2
3
4
5
update table_name set 
                    field1=new-value1, 
                    field2=new-value2,
                    field3=new-value3
                [where Clause]

where条件如果不加，影响所有行

delete操作

1
delete from table_name [where Clause]

where条件如果不加，影响所有行

select操作

1
2
3
4
5
6
7
8
9
10
select column_name,column_name from table_name [where Clause]

select column_name,column_name from table_name [where Clause in (value1,value2,value3)]
//列举查询

select column_name,column_name from table_name [where Clause between value1 and value2]
//区间查询

select column_name,column_name from table_name [where Clause like "%X%"]
//模糊查询

查询模型

在查询时可以将列看做变量进行运算

1
select 列1, 列2, 列1+列2 from table_name [where Clause] [offset M] [limit N]

  • 从表中选出指定的属性值组成一个新表，即为投影；上面的查询语句属于广义投影；

  • where和后面的语句构成一个布尔表达式，解析查询语句时，数据库从头到尾根据where条件遍历整张表的每条记录，如果结果为1，则输出相应的记录结果，如果结果为0，则查看下一条记录，直至查询出结果。例如，查询条件[where 1]将输出整张表，[where 0]将什么也查不到。

奇怪的NUll

1
2
select 列1, 列2 from table_name where 列名 is null
select 列1, 列2 from table_name where 列名 is not null

  • null表示空值，但null=null为假

  • 在设计表时，应尽量不允许使用null

group分组统计

1
2
3
4
5
max()    //求最大值
count()  //求行数
avg()    //求平均数
min()    //求最小值
sum()    //求总和

查询格式

1
select 列1, 列2, sum(列1+列2) from table_name group by 列3
  • group查询时会先根据where条件对数据进行排序，所以，分组查询是是比较耗费数据库系统资源的，应尽量避免

having筛选结果集

1
select 列1, 列2, sum(列1+列2) as X from table_name having [X > 100]

  • 在上式中 sum(列1+列2) 就是结果集，然后重命名为 X ，两者可以看做近似相等，但也有不同之处。

  • where 是对数据表中的原始数据进行筛选，因为X并不是数据表中的原始项，所以会报错。

  • having 是对已经筛选出来的结果集进行再次筛选，如果将其替换为 where 数据库无法执行。

习题练习

name subject score
张三 数学 90
张三 语文 50
张三 地理 40
李四 语文 55
李四 政治 45
王五 政治 30

查询出2门及2门以上不及格者的平均成绩

1
select name,sum(score<60) as gk,avg(score) as pj form resule group by name having gk>=2

先查所有人所有科的平均分，再利用having筛选谁挂科量在2门及其以上

order排序

1
select 列1, 列2 from table_name order by 列3 [asc/desc],列4 [asc/desc]

默认为升序排列【asc】

上式表示先根据列3进行排序，然后结果集再根据列4进行排序，例如，先按照国籍排序，同一国人再按照年龄排序

limit用法

1
select 列1, 列2 from table_name order by 列3 [asc/desc],列4 [asc/desc] limit [偏移量,取出条数]

偏移量表示跳过前几条数据，如果为0则可以不写

子句查询陷阱

1
where -> group by -> having -> order by -> limit

5种子句查询有严格的顺序，不允许随意变换

where型子查询

1
select 列1, 列2 from table_name where 列1 = (select max(列1) from table_name)

内层查询结果作为外层查询条件

from型子查询

1
select 列1 from (select 列1, 列2 from table_name order by 列3)

exists型子查询

1
select * from 表1 where exists (select * from 表2 where 表1.列1 = 表2.列2)

内连接查询

boy表                             girl表
hid|b.name | |hid|g.name
—|—-|—|—|—
A|屌丝||B|小龙女
B|杨过||C|祝英台
C|梁山伯||D|腐女

编号一样表示夫妻关系。要求联表查询出具有夫妻关系的人物名称。

1
2
3
4
select boy.hid,boy.name,girl.his,girl.name 
from
boy inner join girl
on b.hid = girl.hid

执行结果如下
boy.hid|boy.name||girl.hid| girl.name
—|—|—|—|—
B|杨过||B|小龙女
C|梁山伯||C|祝英台

左右连接查询 【继续沿用上面两张表】

左连接：以左表为基准，到右表找匹配的数据，找不到匹配的用NULL补齐。查询格式如下

1
2
3
4
select boy.hid,boy.name,girl.his,girl.name 
from
boy left join girl
on b.hid = girl.hid

boy.hid boy.name girl.hid girl.name
A 屌丝 null null
B 杨过 B 小龙女
C 梁山伯 C 祝英台

右连接：以右表为基准，到左表找匹配的数据，找不到匹配的用NULL补齐。查询格式如下

1
2
3
4
select boy.hid,boy.name,girl.his,girl.name 
from
boy right join girl
on b.hid = girl.hid

boy.hid boy.name girl.hid girl.name
B 杨过 B 小龙女
C 梁山伯 C 祝英台
null null D 腐女

可以连续进行连表查询，但是在写查询语句时，需要将对连续连接的表起别名，加以区分，避免冲突。

union查询

union查询就是把2条或多条的sql查询结果。合并成一个结果集

1
2
3
sql1   -->   N 行 
sql2   -->   M 行 
sql1 union sql2  --> N+M 行

  • 使用前提：各语句取出的列数相同，列名称未必要求一致，列名称会使用是一条sql的列名称为准

  • 使用union时，完全相等的行将会被合并【合并时比较耗时的操作】，一般不让union进行合并，使用“union all”可以避免。

  • union子句中，不用写order by ，因为合并后得到的最后结果可以order by，子句order by失去意义